﻿using Common;
using DataDefinition;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace DALLayer.Base
{
    public abstract class DataAccessBase
    {
        #region [Protected Properties]

        /// <summary>
        /// Gets or sets the data reader.
        /// </summary>
        /// <value>
        /// The data reader.
        /// </value>
        protected OleDbDataReader DataReader { get; set; }

        /// <summary>
        /// Gets or sets the command text builder.
        /// </summary>
        /// <value>
        /// The command text builder.
        /// </value>
        protected StringBuilder CommandTextBuilder { get; set; }

        /// <summary>
        /// Gets or sets the DB params.
        /// </summary>
        /// <value>
        /// The DB params.
        /// </value>
        protected Dictionary<string, object> DBParams { get; set; }

        #endregion

        #region [Private Properties]

        /// <summary>
        /// Gets or sets the connection string.
        /// </summary>
        /// <value>
        /// The connection string.
        /// </value>
        private string ConnectionString { get; set; }

        /// <summary>
        /// Gets or sets the DB connection.
        /// </summary>
        /// <value>
        /// The DB connection.
        /// </value>
        protected OleDbConnection DBConnection { get; set; }

        /// <summary>
        /// Gets or sets the DB command.
        /// </summary>
        /// <value>
        /// The DB command.
        /// </value>
        protected OleDbCommand DBCommand { get; set; }

        #endregion

        #region [Constructor]

        /// <summary>
        /// Initializes a new instance of the <see cref="DataAccessBase" /> class.
        /// </summary>
        public DataAccessBase()
        {
            //Sets the configuration to the ConnectionString property
            this.ConnectionString = AppConfiguration.DBConnectionString;
        }

        #endregion

        #region [Protected Methods]

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <returns>true or false</returns>
        protected bool ExecuteNonQuery()
        {
            try
            {
                //Check db initalization
                if (this.InitializeConnection() == true)
                {
                    //Append a semicolon to the sql string text in case I forget
                    this.CommandTextBuilder.Append(@";");
                    //Create a new OleDbCommand and set it to DBCommand
                    this.DBCommand = new OleDbCommand();
                    //Set the OleDbCommand connection with the existing connection
                    this.DBCommand.Connection = this.DBConnection;
                    //Set the CommandText which will be the sql string
                    this.DBCommand.CommandText = this.CommandTextBuilder.ToString();
                    //Set the CommandType to text
                    this.DBCommand.CommandType = CommandType.Text;

                    //Configure the parameters that has been passed from child
                    this.ConfigureParameters();
                    //ExecuteNonQuery of the OleDbCommand class
                    int result = this.DBCommand.ExecuteNonQuery();
                    if (result == 0)
                    {
                        return false;
                    }
                    else
                    {
                        //Sucessfully executed
                        return true;
                    }
                }
                //Connection failed
                return false;
            }
            catch (Exception eX)
            {
                //log the error here, dont throw the exception
                //Failed to execute query
                return false;
            }
            finally
            {
                this.FinalizeConnection();
            }
        }

       /// <summary>
       /// Exectutes the query and returns the column id
       /// </summary>
       /// <returns>int</returns>
        protected int ExecuteScalar()
        {
            int result = 0;
            try
            {
                //Check db initalization
                if (this.InitializeConnection() == true)
                {
                    //Append a semicolon to the sql string text in case I forget
                    this.CommandTextBuilder.Append(@";");
                    //Create a new OleDbCommand and set it to DBCommand
                    this.DBCommand = new OleDbCommand();
                    //Set the OleDbCommand connection with the existing connection
                    this.DBCommand.Connection = this.DBConnection;
                    //Set the CommandText which will be the sql string
                    this.DBCommand.CommandText = this.CommandTextBuilder.ToString();
                    //Set the CommandType to text
                    this.DBCommand.CommandType = CommandType.Text;

                    //Configure the parameters that has been passed from child
                    this.ConfigureParameters();
                    int rowsAffected = this.DBCommand.ExecuteNonQuery();
                    if (rowsAffected > 0)
                    {
                        //Clear the command text builder
                        this.CommandTextBuilder.Clear();
                        //Sql for identity
                        this.CommandTextBuilder.Append(@"SELECT @@Identity;");
                        //Create a new OleDbCommand and set it to DBCommand
                        this.DBCommand = new OleDbCommand();
                        //Set the OleDbCommand connection with the existing connection
                        this.DBCommand.Connection = this.DBConnection;
                        //Set the CommandText which will be the sql string
                        this.DBCommand.CommandText = this.CommandTextBuilder.ToString();
                        //Set the CommandType to text
                        this.DBCommand.CommandType = CommandType.Text;
                        //ExecuteNonQuery of the OleDbCommand class
                        result = (Int32)this.DBCommand.ExecuteScalar();

                    }
                }
            }
            catch (Exception eX)
            {
                //log the error here, dont throw the exception
                //Failed to execute query
            }
            finally
            {
                this.FinalizeConnection();
            }

            return result;
        }

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <returns></returns>
        protected bool ExecuteReader()
        {
            try
            {
                if (this.InitializeConnection() == true)
                {
                    this.CommandTextBuilder.Append(@";");

                    this.DBCommand = new OleDbCommand();
                    this.DBCommand.Connection = this.DBConnection;
                    this.DBCommand.CommandText = this.CommandTextBuilder.ToString();
                    this.DBCommand.CommandType = CommandType.Text;

                    this.ConfigureParameters();

                    this.DataReader = this.DBCommand.ExecuteReader();

                    return true;
                }

                return false;
            }
            catch (Exception eX)
            {
                //log the error here, dont throw the exception
                return false;
            }
        }

        /// <summary>
        /// Initializes the data access.
        /// </summary>
        protected void InitializeDataAccess()
        {
            try
            {
                //This will hold the sql string
                this.CommandTextBuilder = new StringBuilder();
                //The datareader that will hold the data returned
                this.DataReader = null;
                //The OleDbCommand
                this.DBCommand = null;
                //The parameters for the sql string.
                this.DBParams = new Dictionary<string, object>();
            }
            catch (Exception eX)
            {
                // log the error here
            }
        }

        #endregion

        #region [Private Methods]

        /// <summary>
        /// Initializes the connection.
        /// </summary>
        /// <returns>true or false</returns>
        private bool InitializeConnection()
        {
            try
            {
                //Checks the connection state and if its opened return true
                if (this.DBConnection != null && this.DBConnection.State == ConnectionState.Open)
                {
                    return true;
                }
                //Creates a new connection
                this.DBConnection = new OleDbConnection();
                //Sets the OledDbConnection's connection string, where this.ConnectionString holds the Dbconnection string
                this.DBConnection.ConnectionString = this.ConnectionString;
                //Open the connection
                this.DBConnection.Open();
                //Returns true if connection state is open else false
                return (this.DBConnection.State == ConnectionState.Open);
            }
            catch (Exception eX)
            {
                throw eX;
            }
        }

        /// <summary>
        /// Finalizes the connection.
        /// </summary>
        protected void FinalizeConnection()
        {
            try
            {
                //Check if the connection is open
                if (this.DBConnection != null && this.DBConnection.State == ConnectionState.Open)
                {
                    //Close the connection
                    this.DBConnection.Close();
                }
                //Set the connection to null
                this.DBConnection = null;
            }
            catch (Exception eX)
            {
                //log the error here, dont throw the exception
            }
        }

        /// <summary>
        /// Configures the parameters.
        /// </summary>
        private void ConfigureParameters()
        {
            //Check if there are parameters
            if (this.DBParams != null && this.DBParams.Keys.Count > 0)
            {
                //Loop through the params
                foreach (KeyValuePair<string, object> key in this.DBParams)
                {
                    //Create a OleDbParameter and add the key and value and add it to the command parameters
                    this.DBCommand.Parameters.Add(new OleDbParameter(key.Key, key.Value));
                }
            }
        }

        #endregion
    }
}
